package com.wang.utils

import com.alibaba.druid.pool.DruidDataSource
import com.alibaba.fastjson.JSONObject
import com.wang.template.SQLTemplate
import com.wang.template.entity.Column
import com.wang.template.entity.Table
import java.math.BigInteger
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Timestamp

object DatabaseUtils {
    fun loadDatabase(jsonObject: JSONObject): List<Table> {
        return DatabaseUtils.loadDatabase(
            jsonObject.getString("host"),
            jsonObject.getString("port"),
            jsonObject.getString("database"),
            jsonObject.getString("user"),
            jsonObject.getString("password")
        )
    }

    fun loadDatabase(params: Map<String, String>): List<Table> {
        return DatabaseUtils.loadDatabase(
            params.getValue("host"),
            params.getValue("port"),
            params.getValue("database"),
            params.getValue("user"),
            params.getValue("password")
        )
    }

    // 加载数据库信息
    fun loadDatabase(
        host: String,
        port: String,
        database: String,
        user: String,
        password: String
    ): List<Table> {
        val sql =
            "select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, EXTRA, COLUMN_DEFAULT, COLUMN_KEY from `COLUMNS` where TABLE_SCHEMA = '$database'"

        Class.forName("com.mysql.jdbc.Driver")
        val dbUrl = "jdbc:mysql://$host:$port/information_schema?useSSL=false&serverTimezone=UTC"
        val conn = DriverManager.getConnection(dbUrl, user, password)

        val stmt = conn.createStatement()
        val rs = stmt.executeQuery(sql)

        // 展开结果集数据库
        val columnList = mutableListOf<Column>()
        while (rs.next()) {
            val col = Column()
            // 通过字段检索
            col.tableName = rs.getString("TABLE_NAME")
            col.name = rs.getString("COLUMN_NAME")
            col.type = rs.getString("DATA_TYPE").toUpperCase()
            col.comment = rs.getString("COLUMN_COMMENT")
            col.columnDefault = rs.getString("COLUMN_DEFAULT")
            col.extra = rs.getString("EXTRA")
            col.fieldName = StringUtils.fieldName(col.name)
            col.javaType = javaType(col.type)
            col.primary = rs.getString("COLUMN_KEY").contains("PRI")
            columnList.add(col)
        }
        // 完成后关闭
        rs.close()
        stmt.close()
        conn.close()

        return columnList.groupBy { it.tableName }.entries.map {
            val table = Table()
            table.name = it.key
            table.columnList = it.value.map { col -> col.fieldName to col }.toMap()
            table.primaryKey = it.value.find(Column::getPrimary)
            table
        }
    }

    fun openConnection(jsonObject: JSONObject): Connection {
        return DatabaseUtils.openConnection(
            jsonObject.getString("host"),
            jsonObject.getString("port"),
            jsonObject.getString("database"),
            jsonObject.getString("user"),
            jsonObject.getString("password")
        )
    }

    fun openConnection(params: Map<String, String>): Connection {
        return DatabaseUtils.openConnection(
            params.getValue("host"),
            params.getValue("port"),
            params.getValue("database"),
            params.getValue("user"),
            params.getValue("password")
        )
    }

    fun openConnection(
        localhost: String,
        port: String,
        database: String,
        user: String,
        password: String
    ): Connection {
        Class.forName("com.mysql.jdbc.Driver")
        val dbUrl = "jdbc:mysql://$localhost:$port/$database?useSSL=false&serverTimezone=UTC"
        return DriverManager.getConnection(dbUrl, user, password)
    }

    private fun javaType(s: String) = when (s.toLowerCase()) {
        "varchar", "longtext", "char", "longblob", "mediumtext", "text", "blob", "enum" -> "String"
        "bigint" -> "Long"
        "datetime", "timestamp", "time" -> "Date"
        "int", "tinyint", "smallint" -> "Integer"
        "decimal", "double", "float" -> "BigDecimal"
        else -> throw Exception()
    }

    fun map(rs: ResultSet): List<Map<String, Any>> {
        val colCount = rs.metaData.columnCount
        val list = mutableListOf<Map<String, Any>>()
        while (rs.next()) {
            val map = mutableMapOf<String, Any>()
            for (i in 0 until colCount) {
                val rawV = rs.getObject(i + 1)
                val v = when (rawV) {
                    is BigInteger -> {
                        rawV.longValueExact()
                    }
                    else -> rawV
                }
                val camelFiled = StringUtils.fieldName(rs.metaData.getColumnLabel(i + 1))
                map[camelFiled] = v
            }
            list.add(map)
        }
        return list
    }

    fun <T> map(rs: ResultSet, clazz: Class<T>) = DatabaseUtils.map(rs).map { json ->
        val c = clazz.newInstance()
        clazz.declaredFields.forEach { f ->
            f.isAccessible = true
            f.set(c, json[f.name])
        }
        c
    }

    fun <T> select(conn: Connection, sql: String, clazz: Class<T>): List<T> {
        return map(conn.createStatement().executeQuery(sql), clazz)
    }

    fun select(conn: Connection, sql: String): List<Map<String, Any>> {
        return map(conn.createStatement().executeQuery(sql))
    }

    fun update(conn: Connection, sql: String): Int {
        return conn.createStatement().executeUpdate(sql)
    }

    fun delete(conn: Connection, sql: String): Int {
        return conn.createStatement().executeUpdate(sql)
    }

    fun insert(conn: Connection, sql: String): Int {
        return conn.createStatement().executeUpdate(sql)
    }


    fun dataSource(): DruidDataSource {
        val dataSource = DruidDataSource()
        dataSource.url = "jdbc:mysql://localhost:3306/wdj?useSSL=false&serverTimezone=UTC"
        dataSource.username = "root"
        dataSource.password = "root"
        dataSource.driverClassName = "com.mysql.jdbc.Driver"
        return dataSource
    }
}